﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Windows.Forms;
using System.Data;

namespace KpiConfigProject.DataBase
{
    public class DataBaseManager
    {
        static MySqlConnection connection;

        public static MySqlConnection GetConnection()
        {
            return connection;
        }

        /// <summary>
        /// 
        /// </summary>
        public static void InitDataBase()
        {
            string connString = System.Configuration.ConfigurationSettings.AppSettings.Get("MysqlConnectString");
            try
            {
                connection = new MySqlConnection();
                connection.ConnectionString = connString;
                connection.Open();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }
        /// <summary>
        /// 
        /// </summary>
        public static void CloseDataBase()
        {
            if (connection != null && (connection.State != ConnectionState.Closed || connection.State == ConnectionState.Broken))
            {
                try
                {
                    connection.Close();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                }
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static DataTable ExecuteSql(string sql)
        {
            DataTable table = null, schemaTable = null;
            MySqlCommand cmd = null;
            MySqlDataAdapter adapter = null;
            try
            {
                if (connection != null)
                {
                    cmd = new MySqlCommand(sql, connection);
                    adapter = new MySqlDataAdapter(cmd);
                    table = new DataTable();
                    schemaTable = new DataTable();
                    adapter.Fill(table);
                    adapter.FillSchema(schemaTable, SchemaType.Mapped);
                    table.TableName = schemaTable.TableName;
                    schemaTable.Dispose();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
            return table;
        }

        public static int ExecuteNonSql(string sql)
        {
            MySqlCommand cmd = null;
            int result = 0;
            try
            {
                cmd = new MySqlCommand(sql, connection);
                result = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
            return result;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="rows"></param>
        /// <returns></returns>
        public static bool InsertDataView(List<DataRowView> rows)
        {
            if (rows == null || rows.Count <= 0)
                return true;
            MySqlTransaction transaction = null;
            try
            {
                transaction = connection.BeginTransaction();
                MySqlCommand cmd = new MySqlCommand("", connection);
                StringBuilder insertBuilder = new StringBuilder("insert into ");
                DataTable table = rows[0].Row.Table;
                insertBuilder.Append(table.TableName);
                insertBuilder.Append("(");
                foreach (DataColumn column in table.Columns)
                {
                    insertBuilder.Append(column.ColumnName + ",");
                }
                insertBuilder = insertBuilder.Remove(insertBuilder.Length - 1, 1);
                insertBuilder.Append(") values (");
                StringBuilder dataBuilder = new StringBuilder();
                for (int i = 0; i < rows.Count; i++)
                {
                    dataBuilder.Clear();
                    DataRow row = rows[i].Row;
                    for (int j = 0; j < row.ItemArray.Length; j++)
                    {
                        dataBuilder.Append(GetColumnValue(row.ItemArray[j], table.Columns[j]) + ",");
                    }
                    dataBuilder = dataBuilder.Remove(dataBuilder.Length - 1, 1);
                    cmd.CommandText = insertBuilder.ToString() + dataBuilder.ToString() + ")";
                    cmd.ExecuteNonQuery();
                }
                transaction.Commit();
                return true;
            }
            catch (Exception ex)
            {
                transaction.Rollback();
                MessageBox.Show(ex.ToString());
            }
            finally
            {
                if (transaction != null)
                    transaction.Dispose();
            }
            return false;
        }

        private static string GetColumnValue(object value, DataColumn column)
        {
            if (column.DataType == typeof(int) || column.DataType == typeof(double))
            {
                return value.ToString();
            }
            else
            {
                return "'" + value + "'";
            }
        }

        public static bool UpdateDataView(List<DataRowView> rows)
        {
            if (rows == null || rows.Count <= 0)
                return true;
            MySqlTransaction transaction = null;
            try
            {
                transaction = connection.BeginTransaction();
                MySqlCommand cmd = new MySqlCommand("", connection);
                DataTable table = rows[0].Row.Table;
                StringBuilder updateBuilder = new StringBuilder("update ");
                updateBuilder.Append(table.TableName);
                updateBuilder.Append(" set ");
                StringBuilder dataBuilder = new StringBuilder();
                for (int i = 0; i < rows.Count; i++)
                {
                    dataBuilder.Clear();
                    DataRow row = rows[i].Row;
                    for (int j = 1; j < row.ItemArray.Length; j++)
                    {
                        dataBuilder.Append(table.Columns[j].ColumnName);
                        dataBuilder.Append("=");
                        dataBuilder.Append(GetColumnValue(row.ItemArray[j], table.Columns[j]) + ",");
                    }
                    dataBuilder = dataBuilder.Remove(dataBuilder.Length - 1, 1);
                    dataBuilder.Append(" where ");
                    dataBuilder.Append(table.Columns[0].ColumnName + "=" + row.ItemArray[0]);
                    cmd.CommandText = updateBuilder.ToString() + dataBuilder.ToString();
                    cmd.ExecuteNonQuery();
                }
                transaction.Commit();
                return true;
            }
            catch (Exception ex)
            {
                transaction.Rollback();
                MessageBox.Show(ex.ToString());
            }
            finally
            {
                if (transaction != null)
                    transaction.Dispose();
            }
            return false;
        }

        public static bool DeleteDataView(List<DataRowView> rows)
        {
            if (rows == null || rows.Count <= 0)
                return true;
            MySqlTransaction transaction = null;
            try
            {
                transaction = connection.BeginTransaction();
                MySqlCommand cmd = new MySqlCommand("", connection);
                DataTable table = rows[0].Row.Table;
                StringBuilder deleteBuilder = new StringBuilder("delete from ");
                deleteBuilder.Append(table.TableName);
                deleteBuilder.Append(" where " + table.Columns[0].ColumnName + " in (");
                for (int i = 0; i < rows.Count; i++)
                {
                    deleteBuilder.Append(rows[i].Row.ItemArray[0]+",");
                }
                deleteBuilder = deleteBuilder.Remove(deleteBuilder.Length - 1, 1);
                deleteBuilder.Append(")");
                cmd.CommandText = deleteBuilder.ToString();
                cmd.ExecuteNonQuery();
                transaction.Commit();
                return true;
            }
            catch (Exception ex)
            {
                transaction.Rollback();
                MessageBox.Show(ex.ToString());
            }
            finally
            {
                if (transaction != null)
                    transaction.Dispose();
            }
            return false;
        }
    }
}
